# -*- coding: utf-8 -*-
import traceback
from flask_socketio import SocketIO
from flask import Flask, render_template, request, jsonify, Response, send_file
from io import BytesIO
import sqlite3
import os
from datetime import datetime, timedelta
import requests
import re
import Shanghai_cbfxb as CBFXZB
import Shenzhen_cbfxb  as CBFXZB2
from flask_socketio import emit
from flask import send_from_directory
import Shanghai_get_income as SGI
import Shanghai_get_Out_put as SGO
import Shanghai_get_patent as SGP
import Shanghai_get_cost as SGC
import Shanghai_get_Top_Five_Customers as SGTFC
from collections import OrderedDict
import Shanghai_get_Top_Five_supplier as SGTFS
import Shenzhen_get_income as ZGI
import Shenzhen_get_income_all as ZGIA
import Shenzhen_get_cost as ZGC
import Shenzhen_get_Out_put as ZGOP
import asyncio
from flask import Flask, request, abort
app = Flask(__name__)
blocked_ips=['']
def block_ip(func):
    def wrapper(*args, **kwargs):
        if request.remote_addr in blocked_ips:
            abort(403)  # 返回 403 Forbidden 错误
        return func(*args, **kwargs)
    return wrapper

app.config['SECRET_KEY'] = 'your_secret_key'
socketio = SocketIO(app)
import pdfplumber
import pandas as pd
import subprocess
import numpy as np
from _sqlite3 import connect
import openpyxl
from openpyxl.reader.excel import load_workbook
global tables
import pyodbc
import down_Html as DH
def clean_filename(filename):
    # 使用正则表达式替换非法字符
    filename = filename.replace(":", "：")
    cleaned_filename = re.sub(r'[\/:*?"<>|]', '', filename)
    return cleaned_filename
# 数据库路径
database_path = 'SHPUBSRV01'
database_name = 'JYPRIME'
connection_string = f'DRIVER={{SQL Server}};SERVER={database_path};DATABASE={database_name};Trusted_Connection=yes'
# 连接数据库
def connect_db():
    return pyodbc.connect(connection_string)
def connect_db2():
    database_path= 'SHPUBSRV02'
    database_name='JYCFI'
    connection_string = f'DRIVER={{SQL Server}};SERVER={database_path};DATABASE={database_name};Trusted_Connection=yes'
    return pyodbc.connect(connection_string)
# 路由，用于显示数据库表格
@app.route('/', methods=['GET', 'POST'])
@app.route('/index', methods=['GET', 'POST'])
def display_table():
    if request.method == 'POST':
        igsdm_value = request.form.get('igsdmInput')
        xxly_value = request.form.get('xxlyInput')
        connection = connect_db()
        cursor = connection.cursor()
        if igsdm_value and igsdm_value.strip() and xxly_value and xxly_value.strip():
            # If both igsdm_value and xxly_value are provided, use them in the query
            query = "SELECT top 100 ID,IGSDM,XXBT,CONVERT(varchar(10),XXFBRQ,23) XXFBRQ,CONVERT(varchar(10),JZRQ,23) JZRQ,GGLJ FROM usrGSGGYWFWB WHERE GGLJ IS NOT NULL  AND  IGSDM = ? AND XXBT LIKE ?  "
            cursor.execute(query, (igsdm_value, '%' + xxly_value + '%'))
        elif igsdm_value and igsdm_value.strip():
            # If only igsdm_value is provided, use it in the query
            query = "SELECT top 100 ID,IGSDM,XXBT,CONVERT(varchar(10),XXFBRQ,23) XXFBRQ,CONVERT(varchar(10),JZRQ,23) JZRQ,GGLJ FROM usrGSGGYWFWB WHERE GGLJ IS NOT NULL  AND IGSDM = ?  "
            cursor.execute(query, (igsdm_value,))
        elif xxly_value and xxly_value.strip():
            # If only xxly_value is provided, use it in the query
            query = "SELECT top 100 ID,IGSDM,XXBT,CONVERT(varchar(10),XXFBRQ,23) XXFBRQ,CONVERT(varchar(10),JZRQ,23) JZRQ,GGLJ FROM usrGSGGYWFWB WHERE GGLJ IS NOT NULL   AND XXBT LIKE ? "
            cursor.execute(query, ('%' + xxly_value + '%',))
        else:
            # If no search query is provided, select all data
            cursor.execute(
                'SELECT top 100 ID,IGSDM,XXBT,CONVERT(varchar(10),XXFBRQ,23) XXFBRQ,CONVERT(varchar(10),JZRQ,23) JZRQ,GGLJ FROM usrGSGGYWFWB where GGLJ IS NOT NULL   ')
        column_names = [column[0] for column in cursor.description]
        data = [dict(zip(column_names, row)) for row in cursor.fetchall()]
        connection.close()
        return jsonify(data)
    else:
        connection = connect_db()
        cursor = connection.cursor()
        cursor.execute(
            'SELECT top 100 ID,IGSDM,XXBT,CONVERT(varchar(10),XXFBRQ,23) XXFBRQ,CONVERT(varchar(10),JZRQ,23) JZRQ,GGLJ  FROM usrGSGGYWFWB  WHERE GGLJ IS NOT NULL  AND NRLB IN (5,6) ')
        data = cursor.fetchall()
        connection.close()
        return render_template('index.html', data=data)


@app.route('/download.html', methods=['GET'])
def chooce_download():
    selected_data = request.args.get('data')
    # 在这里你可以使用 selected_data 渲染 download.html 模板
    return render_template('download.html', data=selected_data)
# 路由，用于下载文件
@app.route('/download-all', methods=['POST'])
def download_all():
    data = request.get_json()
    selected_rows = data.get('selectedRows', [])
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.3",
        "Accept-Encoding": "gzip, deflate",
        "Accept": "*/*",
        "Connection": "keep-alive"
    }
    # 创建一个目录用于保存下载的文件，使用相对路径
    download_dir = os.path.join('downloaded_files')
    os.makedirs(download_dir, exist_ok=True)
    # 下载所有文件
    for row in selected_rows:
        xxly = row.get('xxly', '')
        gglj = row.get('gglj', '')
        igsdm = row.get('igsdm', '')
        cleaned_xxly = clean_filename(xxly)
        # 确保每个 igsdm 对应的文件夹存在
        igsdm_dir = os.path.join(download_dir, str(igsdm))
        os.makedirs(igsdm_dir, exist_ok=True)
        try:
            response = requests.get(gglj, stream=True, headers=headers)
            if response.status_code == 200:
                file_name = os.path.join(igsdm_dir, f"{cleaned_xxly}.pdf")
                with open(file_name, 'wb') as file:
                    for chunk in response.iter_content(chunk_size=128):
                        file.write(chunk)
        except Exception as e:
            print(f"response.status_code {response.status_code} Error downloading {gglj}: {str(e)}")
    return jsonify(success=True)


@app.route('/format', methods=['GET'])
def fromat():
    return


@app.route('/chuli.html')
@block_ip
def chuli():
    return render_template('chuli.html')
@socketio.on('connect', namespace='/ykfyyds')
def test_connect():
    emit('my_response', {'data': '请输入ID'})


@app.route('/jysj', methods=['POST'])
def jysj():
    EPBH = request.get_json().get('existingData', '')
    JZRQ= request.get_json().get('JZRQ', '')
    connection = connect_db2()
    EPS=EPBH.split(' ')
    all=pd.DataFrame()
    for EP in EPS:
        print(EP)
        if EPBH !='' and JZRQ =='':
            with open('SQL/Select_lssj', 'r', encoding='utf-8') as file:
                sql = file.read()
            sql = sql.replace('new_condition_here', EP)
        elif EPBH !='' and JZRQ !='':
            with open('SQL/Select_lssj', 'r', encoding='utf-8') as file:
                sql=file.read()
            sql = sql.replace("A.EPBH='new_condition_here'", f"A.EPBH='{EP}' AND A.JZRQ='{JZRQ}'")
        df = pd.read_sql(sql, connection)
        all=pd.concat([all,df])
    connection.close()
    excel_data = BytesIO()
    with pd.ExcelWriter(excel_data, engine='xlsxwriter') as writer:
        for EPBH ,data in all.groupby('EP编号'):
            data.to_excel(writer, sheet_name=EPBH,index=False)
        # all.to_excel(writer, sheet_name='Sheet1',index=False)
    excel_data.seek(0)
    return send_file(excel_data,mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            download_name=f'{EPBH}.xlsx',as_attachment=True)
@app.route('/usrJRFB', methods=['POST'])
def usrJRFB():
    EPBH = request.get_json().get('existingData', '')
    JZRQ= request.get_json().get('JZRQ', '')
    connection = connect_db2()
    EPS=EPBH.split(' ')
    all=pd.DataFrame()
    for EP in EPS:
        if EPBH !='' and JZRQ =='':
            with open('SQL/Select_lssj', 'r', encoding='utf-8') as file:
                sql = file.read()
            sql = sql.replace('new_condition_here', EP)
        elif EPBH !='' and JZRQ !='':
            with open('SQL/Select_lssj', 'r', encoding='utf-8') as file:
                sql=file.read()
            sql = sql.replace("A.EPBH='new_condition_here'", f"A.EPBH='{EP}' AND A.JZRQ='{JZRQ}'")
        df = pd.read_sql(sql, connection)
        all=pd.concat([all,df])
    connection.close()
    excel_data = BytesIO()
    with pd.ExcelWriter(excel_data, engine='xlsxwriter') as writer:
        for EPBH ,data in all.groupby('EP编号'):
            data.to_excel(writer, sheet_name=EPBH,index=False)
        # all.to_excel(writer, sheet_name='Sheet1',index=False)
    excel_data.seek(0)
    return send_file(excel_data,mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            download_name=f'{EPBH}.xlsx',as_attachment=True)
@app.route('/usrcbfxb', methods=['POST'])
def usrcbfxb():
    report_name = request.get_json().get('reportName', '')
    print(report_name)
    connection = connect_db2()
    with open('SQL/CBFXB', 'r', encoding='utf-8') as file:
        sql = file.read()
        sql = sql.replace('new_condition_here', report_name)
    df=pd.read_sql(sql,connection)
    connection.close()
    excel_data = BytesIO()
    df.to_excel(excel_data, index=False)
    excel_data.seek(0)
    return send_file(excel_data, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
                     download_name=f'{report_name}.xlsx', as_attachment=True)
@app.route('/usrht', methods=['POST'])
def usrht():
    print('usrht')
    EPBH = request.get_json().get('existingData', '')
    JZRQ= request.get_json().get('JZRQ', '')
    ZBFL= request.get_json().get('ZBFL', '')
    connection = connect_db2()
    EPS=EPBH.split(' ')
    all=pd.DataFrame()
    for EP in EPS:
        if EP !=''  and ZBFL !='':
            with open('SQL/HTSJ.TXT', 'r', encoding='utf-8') as file:
                sql = file.read()
            sql = sql.replace("EPBH='new_condition_here'", f"EPBH='{EP}'  AND ZBFL='{ZBFL}'")
        elif EP !='' and JZRQ =='':
            with open('SQL/HTSJ.TXT', 'r', encoding='utf-8') as file:
                sql = file.read()
            sql = sql.replace('new_condition_here', EP)
        elif EP !='' and JZRQ !='':
            with open('SQL/HTSJ.TXT', 'r', encoding='utf-8') as file:
                sql=file.read()
            sql = sql.replace("EPBH='new_condition_here'", f"EPBH='{EP}' AND JZRQ='{JZRQ}'")
        df = pd.read_sql(sql, connection)
        all=pd.concat([all,df])


    connection.close()
    excel_data = BytesIO()
    with pd.ExcelWriter(excel_data, engine='xlsxwriter') as writer:
         for EPBH ,data in all.groupby('EPBH'):
             data.to_excel(writer, sheet_name=EPBH,index=False)
    # all.to_excel(excel_data,index=False)
    excel_data.seek(0)
    return send_file(excel_data,mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            download_name=f'{EPBH}.xlsx',as_attachment=True)
@app.route('/usrMX', methods=['POST'])
def usrMX():
    EPBH = request.get_json().get('existingData', '')
    EPS=EPBH.split(' ')
    JZRQ= request.get_json().get('JZRQ', '')
    connection = connect_db2()
    all=pd.DataFrame()
    for EP in EPS:
        if EPBH !='' and JZRQ =='':
            with open('SQL/MXB', 'r', encoding='utf-8') as file:
                sql = file.read()
            sql = sql.replace('new_condition_here', EP)
        elif EPBH !='' and JZRQ !='':
            with open('SQL/MXB', 'r', encoding='utf-8') as file:
                sql=file.read()
            sql = sql.replace("A.EPBH ='new_condition_here'", f"A.EPBH='{EP}' AND A.JZRQ='{JZRQ}'")
            print(sql)
        df = pd.read_sql(sql, connection)
        all=pd.concat([all,df])
    connection.close()
    excel_data = BytesIO()
    with pd.ExcelWriter(excel_data, engine='xlsxwriter') as writer:
        # for EPBH ,data in all.groupby('EP编号'):
        #     data.to_excel(writer, sheet_name=EPBH,index=False)
        all.to_excel(writer, sheet_name='Sheet1',index=False)
    excel_data.seek(0)
    return send_file(excel_data,mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            download_name=f'{EPBH}.xlsx',as_attachment=True)
@app.route('/ykfyyds', methods=['POST'])
def ykfyyds():
        data = request.get_json()
        folder_name = data.get('folderName', '')
        biaogzt=data.get('processType', '')
        zt=data.get('zt', '')
        if zt ==1:
            print('成本分析主表')
        else:
            print('经营数据表')
        if not folder_name:
            return  jsonify({'error': 'No folderName provided'}), 400
        IDS = folder_name.split(' ')
        result = pd.DataFrame()
        sss = len(IDS)
        for ID in IDS:
                print(f'正在处理{ID}')
                try:
                    socketio.emit('my_response', {'data': f'正在处理{ID}'}, namespace='/ykfyyds')
                    connection = connect_db()
                    cursor = connection.cursor()
                    query = """SELECT A.ID,B.HASHCODE FROM usrGSGGYWFWB A
               LEFT JOIN usrGSGGYWFWBFB B
                ON A.ID =B.RID
                WHERE B.WJGS=5 AND A.ID=?"""
                    cursor.execute(query, (ID,))
                    html_ID = cursor.fetchall()
                    if html_ID:
                        JL=[ID]
                        MD5 = html_ID[0][1]
                        html_ID = html_ID[0][0]
                        query1 = "SELECT B.ZQSC from usrGSGGYWFWB A left join usrZQZB B on A.IGSDM = B.IGSDM WHERE A.GKBZ=3 AND B.GKBZ=3 AND B.ZQLB=1 AND B.ZQSC IN (90,83) AND B.SSZT=1 AND A.ID =?"
                        cursor.execute(query1, (ID,))
                        zqsc = cursor.fetchall()[0][0]
                        #获取EPBH
                        EPBH_query = "SELECT DISTINCT EPBH FROM usrGSGGYWFWB A left join usrQYMB B ON A.IGSDM=B.QYBH  WHERE A.ID=?"
                        cursor.execute(EPBH_query, (ID,))
                        EPBH = cursor.fetchall()[0][0]
                        #获取历史数据
                        klyysj_query = \
                            "SELECT EPBH,XXLY," \
                            "CONVERT(varchar(10),XXFBRQ,23) AS XXFBRQ," \
                            "CONVERT(varchar(10),JZRQ,23) AS JZRQ," \
                            "C.CHINAME AS JYYWLXDM," \
                            "CASE WHEN SJLMY = 'FCC000001015' THEN '产品' ELSE SJLMY END AS SJLMY, " \
                            "SJLMYMC,SJLMYDM," \
                            "CASE WHEN SJLME = 'FCC000000XZU' THEN '地区' ELSE SJLME END AS SJLME, " \
                            "SJLMEMC,SJLMEDM," \
                            "CASE WHEN SJLMS = 'FBT000000036' THEN '其他'  " \
                            "     WHEN SJLMS = 'FCC0000014ES' THEN '销售模式' " \
                            "     END AS SJLMS" \
                            ",SJLMSMC,ZTYSMC,ZBDM,ZBMC,ZBSJ," \
                            "B.CHINAME AS ZBDW," \
                            "G.CHINAME AS TJKJ," \
                            "H.CHINAME AS TJQJ " \
                            "FROM [10.106.22.60].JYCFI.dbo.usrQYJYSJ A " \
                            "JOIN [10.106.22.60].JYCFI.dbo.usrFCWXXMB B ON A.ZBDW=B.GILCODE " \
                            "JOIN [10.106.22.60].JYCFI.dbo.usrFCWXXMB C ON A.JYYWLXDM=C.GILCODE " \
                            "JOIN [10.106.22.60].JYCFI.dbo.usrFCWXXMB G ON A.TJKJ=G.GILCODE " \
                            "JOIN [10.106.22.60].JYCFI.dbo.usrFCWXXMB H ON A.TJQJ=H.GILCODE " \
                            " WHERE A.GKBZ=3 AND A.EPBH=? " \
                            "order by A.JZRQ,A.YM"
                        klyysj = pd.read_sql_query(klyysj_query, connection, params=(EPBH,))
                        sfybg = DH.download(ID=ID, MD5=MD5)
                        if sfybg == 1:
                            print('下载成功')
                        else:
                            query5 = """SELECT A.ID,B.HASHCODE FROM usrGSGGYWFWB A
                                       LEFT JOIN usrGGWJB B
                                        ON A.ID =B.RID
                                        WHERE B.WJGS=5 AND A.ID=?"""
                            cursor.execute(query5, (ID,))
                            html_ID = cursor.fetchall()
                            try:
                                MD5 = html_ID[0][1]
                                html_ID = html_ID[0][0]
                            except:
                                pass

                            try:
                                os.remove(f"D:/软件打包/Juno-win32.win32.x86_64/Juno-win32.win32.x86_64/{ID}.HTML")
                                sfybg = DH.download(ID=ID, MD5=MD5)
                            except:
                                pass
                        Html_file = f'D:\软件打包\Juno-win32.win32.x86_64\Juno-win32.win32.x86_64\{ID}.HTML'
                        if zt == 0:
                            if zqsc in (83, 90):
                                print(f'证券市场{zqsc}')

                                if zqsc == 83:
                                    try:
                                        yysr = SGI.run_all(ID, Html_file=Html_file)
                                        if yysr.shape[0] != 0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}营收表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        pass
                                        socketio.emit('my_response', {'data': f'{ID}营收表模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        cncl = SGO.run_all(ID, Html_file=Html_file)
                                        if cncl.shape[0]!=0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}产能产量表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        socketio.emit('my_response', {'data': f'{ID}产能产量模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        cbfxb = SGC.run_all(ID, Html_file=Html_file)
                                        if cbfxb.shape[0]!=0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}成本分析表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        socketio.emit('my_response', {'data': f'{ID}成本分析表模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        zl = SGP.run_all(ID, Html_file=Html_file)
                                        if zl.shape[0]!=0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}专利表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        socketio.emit('my_response', {'data': f'{ID}专利表模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        qwdkh = SGTFC.run_all(ID, Html_file=Html_file)
                                        if qwdkh.shape[0]!=0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}前五大客户表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        pass
                                        socketio.emit('my_response', {'data': f'{ID}前五大客户表模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        qwdgys = SGTFS.run_all(ID, Html_file=Html_file)
                                        if qwdgys.shape[0]!=0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}前五大供应商表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        pass
                                        socketio.emit('my_response', {'data': f'{ID}前五大供应商表模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        all = pd.concat([yysr, cncl, cbfxb, zl, qwdkh, qwdgys]).replace('不适用', '', regex=True)
                                    except:
                                        socketio.emit('my_response', {'data': f'{ID}未抽取到数据，请人工核查'},
                                                      namespace='/ykfyyds')
                                        pass
                                    try:
                                        all['YM'] = None
                                        all['YM'] = range(1, len(all) + 1)
                                        all['SFYX'] = None
                                        # 使用 .loc 或 .iloc 并结合索引器来设置值
                                        all.loc[:, 'SFYX'] = 'FCC000000006'
                                    except:
                                        pass
                                    try:
                                        JZRQ = all['JZRQ'].max()
                                    except:
                                        pass
                                    try:
                                        result = pd.concat([result, all])
                                    except:
                                        pass
                                    # JZRQ = datetime.strptime(JZRQ, '%Y-%m-%d')
                                    # JZRQ = JZRQ - timedelta(days=365 * 1)
                                    # JZRQ = JZRQ.date().strftime('%Y-%m-%d')
                                    # JZRQ = str(JZRQ)
                                    # sqsj = klyysj[(klyysj['JZRQ'] == JZRQ) & (klyysj['XXLY'].str.contains('年度'))]
                                    # field_names = ['JYYWLXDM', 'SJLMY', 'SJLMYMC', 'SJLME', 'SJLMEMC', 'SJLMS',
                                    #                'SJLMSMC', 'ZTYSMC', 'ZBDM', 'TJKJ', 'TJQJ']

                                    # result = result.copy()
                                    # result.loc[:, 'concatenated_values'] = result[field_names].apply(
                                    #     lambda row: ''.join(['NA' if pd.isna(val) else val for val in row]), axis=1)
                                    # result['concatenated_values'] = result['concatenated_values']
                                    # result.to_excel('测试.xlsx', index=False)
                                    # result = result.sort_values(by='JZRQ').drop_duplicates(subset='concatenated_values',
                                    #                                                    keep='last')

                                    # result.drop('concatenated_values', axis=1, inplace=True)
                                # except:
                                elif zqsc == 90:
                                    try:
                                        yysr_all = ZGIA.run_all(ID, Html_file=Html_file)
                                        if yysr_all.shape[0]!=0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}全量营收表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        pass
                                        socketio.emit('my_response', {'data': f'{ID}全量营收表模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        yysr = ZGI.run_all(ID, Html_file=Html_file)
                                        if yysr.shape[0]!=0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}营收表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        pass
                                        socketio.emit('my_response', {'data': f'{ID}营收表模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        cncl = ZGOP.run_all(ID, Html_file=Html_file)
                                        if cncl.shape[0]!=0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}产能产量表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        pass
                                        socketio.emit('my_response', {'data': f'{ID}产能产量表模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        cbfxb = ZGC.run_all(ID, Html_file=Html_file)
                                        if cbfxb.shape[0]!=0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}成本分析表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        pass
                                        socketio.emit('my_response', {'data': f'{ID}成本分析表模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        qwdkh = SGTFC.run_all(ID, Html_file=Html_file)
                                        if qwdkh.shape[0]!=0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}前五大客户表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        pass
                                        socketio.emit('my_response', {'data': f'{ID}前五大客户表模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        qwdgys = SGTFS.run_all(ID, Html_file=Html_file)
                                        if qwdgys.shape[0]!=0:
                                            JL.append('1')
                                            socketio.emit('my_response', {'data': f'{ID}前五大供应商表抽取成功'}, namespace='/ykfyyds')
                                        else:
                                            JL.append('0')
                                    except:
                                        pass
                                        socketio.emit('my_response', {'data': f'{ID}前五大供应商表模板错误'}, namespace='/ykfyyds')
                                        JL.append('0')
                                    try:
                                        all = pd.concat([yysr_all, yysr, cncl, cbfxb, qwdkh, qwdgys]).replace('不适用', '',regex=True)

                                        all['YM'] = None
                                        all['YM'] = range(1, len(all) + 1)
                                        all['SFYX'] = None
                                        all['SFYX'].iloc[:len(all)] = 'FCC000000006'
                                        klyysj['ZBDM'] = klyysj['ZBDM'].fillna('')
                                        JZRQ = all['JZRQ'].max()
                                    # JZRQ = datetime.strptime(JZRQ, '%Y-%m-%d')
                                    # JZRQ = JZRQ - timedelta(days=365 * 1)
                                    # JZRQ = JZRQ.date().strftime('%Y-%m-%d')
                                    # JZRQ=str(JZRQ)
                                    # sqsj = klyysj[(klyysj['JZRQ'] == JZRQ) & (klyysj['XXLY'].str.contains('年度'))]
                                    # field_names = ['JYYWLXDM', 'SJLMY', 'SJLMYMC', 'SJLME', 'SJLMEMC', 'SJLMS',
                                    #                'SJLMSMC', 'ZTYSMC', 'ZBDM', 'TJKJ', 'TJQJ']
                                    #
                                    # sqsj_copy = sqsj.copy()
                                    #
                                    # sqsj_copy.loc[:, 'concatenated_values'] = sqsj_copy[field_names].apply(
                                    #     lambda row: ''.join(['NA' if pd.isna(val) else val for val in row]), axis=1)
                                    # sqsj['concatenated_values'] = sqsj_copy['concatenated_values']
                                    #
                                    # sqsj = sqsj.sort_values(by='JZRQ').drop_duplicates(subset='concatenated_values',
                                    #                                                keep='last')
                                    # sqsj.drop('concatenated_values', axis=1, inplace=True)
                                    # print(sqsj)

                                        result = pd.concat([result, all])
                                    except:
                                          pass
                                    try:
                                        result.drop('WYXZD', axis=1,inplace=True)
                                    except:
                                        pass
                                    try:
                                        result.drop('理论统计口径', axis=1, inplace=True)
                                    except:
                                        pass
                                    try:
                                        result.drop('理论统计期间', axis=1, inplace=True)
                                    except:
                                        pass
                                    try:
                                        result.drop('WYXZD', axis=1, inplace=True)
                                    except:
                                        pass
                                    try:
                                        result.drop('WYXZD', axis=1, inplace=True)
                                    except:
                                        pass
                                db_name = r'\\10.3.2.15\固收部\其他\经营指标\自动化标准化存档\mydatabase.db'
                                db_connection = connect(db_name)
                                db_cursor = db_connection.cursor()
                                db_cursor.execute("DELETE FROM zx")
                                db_connection.commit()
                                B = 'Select BBZHMC,BZHMC,LM from BZH WHERE EPBH=?'
                                BZH = db_cursor.execute(B, (EPBH,))
                                BZH = BZH.fetchall()
                                try:
                                    result['SJLMYMC'] = result['SJLMYMC'].replace('：', ':').replace('收入', '')
                                    result = result.replace('海外', '国外').replace('国外销售', '国外').replace(
                                        '国内销售', '国内')
                                except:
                                    pass
                                if result.shape[1]!=0 and zt==0:
                                    result['ZBSJ'] = result['ZBSJ'].replace('%', '', regex=True).replace('%', '', regex=True)
                                    result['SJLMYMC'] = result['SJLMYMC'].replace('-', ':')
                                    for i in BZH:
                                        if   (i[2] == '' or i[2] is None):
                                            result = result.replace(i[0], i[1],regex=True)
                                        elif i[2]=='1':
                                            result['SJLMYMC'] = result['SJLMYMC'].replace(i[0], i[1],regex=True)
                                        elif i[2]=='2':
                                            result['SJLMEMC'] = result['SJLMEMC'].replace(i[0], i[1],regex=True)
                                        elif i[2]=='3':
                                            result['SJLMSMC'] = result['SJLMSMC'].replace(i[0], i[1],regex=True)
                                        elif i[2]=='4':
                                            result['ZTYSMC'] = result['ZTYSMC'].replace(i[0], i[1],regex=True)
                                        elif i[2]=='5':
                                            result['ZBMC'] = result['ZBMC'].replace(i[0], i[1],regex=True)
                                if len(klyysj) != 0 and result.shape[0] != 0 and zt==0:
                                    klyysj =  klyysj[klyysj['JZRQ'] < JZRQ]
                                    all = pd.concat([result, klyysj])
                                    all = all.astype(str)
                                    all = all.applymap(str)
                                    all.fillna('', inplace=True)
                                    all = all.replace('None', '', regex=True)
                                    all = all.replace('nan', '', regex=True)
                                    query2 = 'DELETE FROM TEMP;'
                                    db_cursor.execute(query2)
                                    all.to_sql('zx', db_connection, if_exists='append', index=False)
                                    query3 = """SELECT DISTINCT * FROM zx Q WHERE TJQJ IN ('年', '半年') AND EPBH =?"""
                                    Temp = pd.read_sql(query3, db_connection, params=(EPBH,))
                                    Temp.to_sql('TEMP', db_connection, if_exists='append', index=False)
                                    with open('SQL/ZX', 'r', encoding='utf-8') as file1:
                                        query4 = file1.read()
                                    TJKJ_TJQJ = pd.read_sql(query4, db_connection)
                                    TJKJ_TJQJ.to_excel('TJKJ_TJQJ.xlsx', index=False)
                                    result.fillna('', inplace=True)
                                    result['WYXZD'] = result['EPBH'] + result['JZRQ'] + result['JYYWLXDM'] + result['SJLMYMC'] + \
                                                      result['SJLMEMC'] + result['SJLMSMC'] + result['ZTYSMC'] + result[
                                                          'ZBDM'] + result['TJKJ'] + result['TJQJ']
                                    result = result.merge(TJKJ_TJQJ[['WYXZD', '理论统计口径', '理论统计期间']], on='WYXZD',
                                                          how='left')
                                    result.to_excel('测试.xlsx', index=False)
                                    result['TJQJ'] = result.apply(lambda row: row['理论统计期间'] if pd.notna(row['理论统计期间']) else row['TJQJ'], axis=1)
                                    result['TJKJ'] = result.apply(lambda row: row['理论统计口径'] if pd.notna(row['理论统计口径']) else row['TJKJ'], axis=1)
                                    result.to_excel('TJKJ_TJQJ_result.xlsx', index=False)
                                    result.drop(['WYXZD', '理论统计期间', '理论统计口径'], axis=1, inplace=True)
                                elif len(klyysj) == 0 and result.shape[0] != 0 and sss > 1 and zt==0:
                                    all = result
                                    all = all.astype(str)
                                    all = all.applymap(str)
                                    all.fillna('', inplace=True)
                                    all = all.replace('None', '', regex=True)
                                    all = all.replace('nan', '', regex=True)
                                    query2 = 'DELETE FROM TEMP;'
                                    db_cursor.execute(query2)

                                    try:
                                        all = all.drop(
                                            ['WYXZD', '理论统计口径', '理论统计期间', 'SJLMYMCPPDM', 'SJLMEMCPPDM'],axis=1)
                                    except:
                                        pass
                                    all.to_sql('zx', db_connection, if_exists='append', index=False)
                                    query3 = """SELECT DISTINCT * FROM zx Q WHERE TJQJ IN ('年', '半年') AND EPBH =?"""
                                    Temp = pd.read_sql(query3, db_connection, params=(EPBH,))
                                    Temp.to_sql('TEMP', db_connection, if_exists='append', index=False)
                                    with open('SQL/ZX', 'r', encoding='utf-8') as file:
                                        query4 = file.read()
                                    TJKJ_TJQJ = pd.read_sql(query4, db_connection)
                                    TJKJ_TJQJ.to_excel('TJKJ_TJQJ.xlsx', index=False)
                                    result.fillna('', inplace=True)
                                    result['WYXZD'] = result['EPBH'] + result['JZRQ'] + result['JYYWLXDM'] + result['SJLMYMC'] + \
                                                      result['SJLMEMC'] + result['SJLMSMC'] + result['ZTYSMC'] + result[
                                                          'ZBDM'] + \
                                                      result['TJKJ'] + result['TJQJ']
                                    result = result.merge(TJKJ_TJQJ[['WYXZD', '理论统计口径', '理论统计期间']], on='WYXZD',
                                                          how='left')

                                    result['TJQJ'].fillna(result['理论统计期间'], inplace=True)
                                    result['TJKJ'].fillna(result['理论统计口径'], inplace=True)
                                    result.to_excel('测试.xlsx', index=False)
                                    result.drop(['WYXZD', '理论统计期间', '理论统计口径'], axis=1, inplace=True)
                            Product = "SELECT * FROM product_code"
                            PRODUCT_CODE = pd.read_sql(Product, db_connection)
                            DQ = 'SELECT * FROM city_code'
                            DQ_CODE = pd.read_sql(DQ, db_connection)
                            if result.shape[0]!=0 and zt==0:
                                try:
                                    for index, row in result.iterrows():
                                        value = row['SJLMYMC']
                                        if ':' in value:
                                            # 如果存在冒号，则以冒号为分隔符进行分割，并取最后一个分割后的部分作为新字段的值
                                            split_values = value.split(':')
                                            new_value = split_values[-1]
                                            result.at[index, 'SJLMYMCPPDM'] = new_value
                                        else:
                                            # 如果不存在冒号，则直接将原始值作为新字段的值
                                            result.at[index, 'SJLMYMCPPDM'] = value
                                        value = row['SJLMEMC']
                                        if ':' in value:
                                            # 如果存在冒号，则以冒号为分隔符进行分割，并取最后一个分割后的部分作为新字段的值
                                            split_values = value.split(':')
                                            new_value = split_values[-1]
                                            result.at[index, 'SJLMEMCPPDM'] = new_value
                                        else:
                                            # 如果不存在冒号，则直接将原始值作为新字段的值
                                            result.at[index, 'SJLMEMCPPDM'] = value
                                    result = result.reset_index(drop=True)
                                    result['SJLMYDM'] = result.merge(PRODUCT_CODE, left_on='SJLMYMCPPDM', right_on='SJLMYMC', how='left')['标准代码']
                                    result['SJLMEDM']=pd.merge(result,DQ_CODE,left_on='SJLMEMCPPDM',right_on='SJLMEMC',how='left')['DM']
                                    try:
                                        result=result.drop(['SJLMYMCPPDM','SJLMEMCPPDM'],axis=1)
                                    except:
                                        pass
                                except:
                                    print(EOFError)
                                    cx_JZRQ=result['JZRQ']
                            db_connection.close()
                        elif zt == 1:
                            if zqsc == 83:
                                try:
                                    all=CBFXZB.run_all(ID,Html_file)
                                except:
                                    pass
                            elif zqsc == 90:
                                try:
                                    all=CBFXZB2.run_all(ID,Html_file)
                                except:
                                    pass
                            try:
                                result=pd.concat([all,result])
                            except:pass
                            try:
                                result.replace('%', '', regex=True)
                            except:
                                pass
                except:
                    traceback.print_exc()
                    socketio.emit('my_response', {'data': f'处理{ID}时出错'}, namespace='/ykfyyds')
                    return jsonify({'error': '处理错误'}), 404
                    pass

        excel_data = BytesIO()
        if biaogzt=='ykfhs':
            if zt==0:
                with pd.ExcelWriter(excel_data,engine='xlsxwriter') as writer:
                    print(result)
                    for EPBH, data in result.groupby('EPBH'):
                        data.to_excel(writer, sheet_name=EPBH, index=False)
            if zt ==1:
                with pd.ExcelWriter(excel_data,engine='xlsxwriter') as writer:
                    for IGSDM, data in result.groupby('IGSDM'):
                        data.to_excel(writer, sheet_name=str(IGSDM), index=False)

        else:

            result.to_excel(excel_data, index=False)
        excel_data.seek(0)
        return send_file(
            excel_data,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            download_name=f'{EPBH}.xlsx',
            as_attachment=True)
@app.route('/Biaozh')
def Biaozh():
    return render_template('Biaozh.html')
@app.route('/upload-data', methods=['POST'])
def upload_data():
    try:
        db_name = r'\\10.3.2.15\固收部\其他\经营指标\自动化标准化存档\mydatabase.db'
        con = connect(db_name)
        data = request.json.get('data', [])
        for row in data:
            epbh = row.get('EPBH', '')
            bbzhmc = row.get('BBZHMC', '')
            bzhmc = row.get('BZHMC', '')
            lm=row.get('LM','')
            if epbh != '' and bbzhmc != '' and bzhmc != '':
                con.execute('INSERT OR IGNORE INTO BZH (EPBH, BBZHMC, BZHMC, LM) VALUES (?, ?, ?, ?)', (
                    epbh, bbzhmc, bzhmc, lm))

        con.commit()
        con.close()
        return jsonify({'success': True})
    except Exception as e:
        print(f"错误：{e}")
        return jsonify({'success': False})
if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5001)